| |
| EMP |
| ID |
Country |
Capital |
| 1 |
France |
Paris |
| 2 |
USA |
Washington |
| 3 |
France |
Paris |
| 4 |
India |
New Delhi |
| 5 |
USA |
Washington |
|
---- We can use this method even if we don't have ID Column and Duplicate Rows occurs 2 or more times
---- Count Duplicate Rows
SELECT Country, Capital, COUNT(*) as Occurs
FROM EMP
GROUP BY Country, Capital
HAVING COUNT(*) > 1
| Output |
| Country |
Capital |
Occurs |
| France |
Paris |
2 |
| USA |
Washington |
2 |
---- Delete Duplicate Rows
WITH CTE (Country,Capital, DuplicateCount)
AS
(
SELECT Country,Capital,
ROW_NUMBER() OVER(PARTITION BY Country,Capital ORDER BY Country) AS DuplicateCount
FROM EMP
)
DELETE FROM CTE
WHERE DuplicateCount > 1
|
| Output |
| Country |
Capital |
| France |
Paris |
| India |
New Delhi |
| USA |
Washington |
|